﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using System.Data.OleDb;
using Maticsoft.DBUtility;

namespace Maticsoft.Web.CERTI
{
    public partial class import : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

                if (Session["u"] == null)
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "", "if(top){top.location.href='../Login.aspx'}else{location.href='../Login.aspx'};", true);
                }

            }
        }


        protected void btnSave_Click(object sender, EventArgs e)
        {

            if (FileUpload1.HasFile)
            {
                if (Path.GetExtension(FileUpload1.FileName).Trim().ToLower().Equals(".xls") || Path.GetExtension(FileUpload1.FileName).Trim().ToLower().Equals(".xlsx"))
                {

                    try
                    {
                        string filename = DateTime.Now.ToString("yyyyMMddHHmmssssfff") + Path.GetExtension(FileUpload1.FileName).Trim().ToLower();
                        string pat = Server.MapPath("~/Excel/");
                        FileUpload1.SaveAs(pat + filename);
                        //DataTable dt = GetExcelDataTable(FileUpload1.PostedFile.FileName, "Sheet1");
                        DataTable dt = GetExcelDataTable(pat + filename, "Sheet1");

                        dt = (ClearNullData(dt));
                        //DataView dv=dt.DefaultView;


                        int res = insertdatas(dt);

                        Label1.Text = "Excel中共包含有效数据 " + dt.Rows.Count + " 条,成功导入：" + res + " 条数据&nbsp;&nbsp;&nbsp;&nbsp;未导入：" + (dt.Rows.Count - res) + "条数据&nbsp;&nbsp;&nbsp;&nbsp;其中 " + exist + " 条数据已存在";
                    }
                    catch (Exception ex)
                    {

                        Label1.Text = "导入失败，错误信息如下： <br /><span style='color:red'>" + ex.Message + "<br />" + ex.Source + "</span>";
                    }

                }
                else
                {
                    Maticsoft.Common.MessageBox.ShowAndRedirect(this, "请选择正确的 Excel 文件！", "Add.aspx");

                }

            }
            else
            {
                Maticsoft.Common.MessageBox.ShowAndRedirect(this, "请选择文件！", "Add.aspx");

            }
            //Maticsoft.Common.MessageBox.ShowAndRedirect(this, "保存成功！", "list.aspx");

        }


        public void btnCancle_Click(object sender, EventArgs e)
        {
            Response.Redirect("list.aspx");
        }

        public DataTable ClearNullData(DataTable dt)
        {
            int x = dt.Rows.Count;
            DataTable da = new DataTable();
            int y = 0;
            for (int i = 0; i < x; i++)
            {
                if (dt.Rows[i][0] == null)
                {
                    dt.Rows.RemoveAt(i);
                }
                if (dt.Rows[i][0] != null && dt.Rows[i][0].ToString().Trim().Trim().Trim().Length < 2)
                {
                    //dt.Rows.RemoveAt(i);
                    dt.Rows[i].Delete();
                    //dt.Rows.Remove(dt.Rows[i]);

                    //da.Rows.Add(dt.Rows[i]);
                    //da.ImportRow(dt.Rows[i]);

                    y++;
                }
            }

            //DataTable daa = dt.Clone();
            //foreach (DataRow dr in dt.Rows)
            //{
            //    if (dr != null && dr[0].ToString().Trim().Trim().Length > 2)
            //    {
            //        //dt.Rows.RemoveAt(i);
            //        //da.Rows.Add(dt.Rows[i]);
            //        //da.ImportRow(dr);
            //        //dt.Rows.Remove(dr);

            //        DataRow r = da.NewRow();
            //        r = dr;
            //        da.Rows.Add(r);
            //        //y++;
            //    }

            //}
            dt.AcceptChanges();
            return dt;
        }

        public DataTable GetExcelDataTable(string path, string tname)
        {
            /*Office 2007*/
            string ace = "Microsoft.ACE.OLEDB.12.0";
            /*Office 97 - 2003*/
            string jet = "Microsoft.Jet.OLEDB.4.0";
            string xl2007 = "Excel 12.0 Xml";
            string xl2003 = "Excel 8.0";
            string imex = "IMEX=1";
            /* csv */
            string text = "text";
            string fmt = "FMT=Delimited";
            string hdr = "Yes";
            string conn = "Provider={0};Data Source={1};Extended Properties=\"{2};HDR={3};{4}\";";
            string select = string.Format("SELECT * FROM [{0}$]", tname);
            //string select = sql;
            string ext = Path.GetExtension(path);
            OleDbDataAdapter oda;
            DataTable dt = new DataTable("data");
            switch (ext.ToLower())
            {
                case ".xlsx":
                    conn = String.Format(conn, ace, Path.GetFullPath(path), xl2007, hdr, imex);
                    break;
                case ".xls":
                    conn = String.Format(conn, jet, Path.GetFullPath(path), xl2003, hdr, imex);
                    break;
                case ".csv":
                    conn = String.Format(conn, jet, Path.GetDirectoryName(path), text, hdr, fmt);
                    //sheet = Path.GetFileName(path);
                    break;
                default:
                    break;
                //throw new Exception("File Not Supported!");
            }
            OleDbConnection con = new OleDbConnection(conn);
            con.Open();
            //select = string.Format(select, sql);
            oda = new OleDbDataAdapter(select, con);
            oda.Fill(dt);
            con.Close();
            return dt;
        }

        int exist = 0;
        public int insertdatas(DataTable datatable)
        {
            Maticsoft.BLL.CERTI bll = new Maticsoft.BLL.CERTI();
            int a = 0;

            string delete = "delete from CERTI where ZSH='{0}'";

            string select = "select count(0) from CERTI where ZSH='{0}'";

            if (datatable.Columns.Count == 7)
            {
                foreach (DataRow datarow in datatable.Rows)
                {
                    string sql = "INSERT INTO CERTI(ZSH,JCS,PM,JJZ,BM,CREATEDATE,BZ)" + "VALUES('" + datarow[0].ToString().Trim() + "'" + ",'" + datarow[1].ToString() + "'" + ",'" + datarow[2].ToString() + "'" + ",'" + datarow[3].ToString() + "'" + ",'" + datarow[4].ToString() + "',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'" + datarow[5].ToString() + "')";

                    try
                    {
                        //a = (a + (DbHelperOra.ExecuteSql(sql,"")));
                        //DbHelperOra.ExecuteSql(sql, "");
                        if (RadioButton1.Checked)
                        {
                            bll.Add(string.Format(delete, datarow[0].ToString().Trim()), "");
                            a += bll.Add(sql, "");
                        }
                        else
                        {
                            int rest = Convert.ToInt32(bll.GetSingle(string.Format(select, datarow[0].ToString().Trim()), ""));

                            if (rest < 1)
                            {
                                a += bll.Add(sql, "");
                            }
                            else
                            {
                                exist++;
                            }

                        }

                    }
                    catch (Exception)
                    {

                        a--;
                    }
                }
            }
            else if (datatable.Columns.Count == 4)
            {

                foreach (DataRow datarow in datatable.Rows)
                {
                    string sql = "INSERT INTO CERTI(ZSH,JCS,PM,JJZ,CREATEDATE)" + "VALUES('" + datarow[0].ToString().Trim() + "'" + ",'" + datarow[1].ToString() + "'" + ",'" + datarow[2].ToString() + "'" + ",'" + datarow[3].ToString() + "',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'))";

                    try
                    {
                        //a = (a + (DbHelperOra.ExecuteSql(sql,"")));
                        //DbHelperOra.ExecuteSql(sql, "");
                        //bll.Add(string.Format(delete, datarow[0].ToString().Trim()), "");
                        if (RadioButton1.Checked)
                        {
                            bll.Add(string.Format(delete, datarow[0].ToString().Trim()), "");

                            a += bll.Add(sql, "");
                        }
                        else
                        {
                            int rest = Convert.ToInt32(bll.GetSingle(string.Format(select, datarow[0].ToString().Trim()), ""));
                            if (rest < 1)
                            {
                                a += bll.Add(sql, "");
                            }
                            else
                            {
                                exist++;
                            }
                        }

                    }
                    catch (Exception)
                    {

                        a--;
                    }
                }
            }






            return a;
        }

    }
}
